using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using QLVMBAYDomain;
using System.Data.SqlClient;
using System.Configuration;
namespace QLVMBAYDatacess
{
    public class HanhKhachDA : DataAccessBase
    {
		#region Constructors
		
		public HanhKhachDA() : base()
        {
			_selectSql = "Select * From HANHKHACH";
			_insertSql = "Insert Into HANHKHACH(MAHK,TENHK,NGAYSINH,GIOITINH,HOCHIEU,CMND,DIACHI,DIENTHOAI,EMAIL,NGAYDANGKY) Values (@MAHK,@TENHK,@NGAYSINH,@GIOITINH,@HOCHIEU,@CMND,@DIACHI,@DIENTHOAI,@EMAIL,@NGAYDANGKY)";
        	_updateSql = "Update HANHKHACH Set TENHK=@TENHK,NGAYSINH=@NGAYSINH,GIOITINH=@GIOITINH,HOCHIEU=@HOCHIEU,CMND=@CMND,DIACHI=@DIACHI,DIENTHOAI=@DIENTHOAI,EMAIL=@EMAIL,NGAYDANGKY=@NGAYDANGKY Where 1=1 And MAHK=@MAHK";
        	_deleteSql = "Delete From HANHKHACH Where 1=1 And MAHK=@MAHK";
        	_primarSql = "Select * From HANHKHACH Where 1=1 And MAHK=@MAHK";
        }
		
		protected static HanhKhachDA _inst = null;
		public static HanhKhachDA Inst
		{
			get
			{
				if(_inst == null)
					_inst = new HanhKhachDA();
				return _inst;
			}
		}
		
		#endregion
		
        #region Get Methods

		private HanhKhach GetCurrent(IDataReader reader, bool isDeep)
		{
			HanhKhach ret = new HanhKhach();
			ret.MaHanhKhach = (string)reader["MAHK"];
			ret.TenHanhKhach = (string)reader["TENHK"];
			ret.NgaySinh = reader["NGAYSINH"].ToString();
			ret.GioiTinh = (int)reader["GIOITINH"];
			ret.HoChieu = (string)reader["HOCHIEU"];
			ret.CMND = (string)reader["CMND"];
			ret.DiaChi = (string)reader["DIACHI"];
			ret.DienThoai = (string)reader["DIENTHOAI"];
			ret.Email = (string)reader["EMAIL"];
			ret.NgayDangKy = (System.DateTime)reader["NGAYDANGKY"];
			
			
			return ret;
		}

		public List<HanhKhach> GetAll(bool isDeep)
		{
			DbCommand comm = GetCommand(_selectSql);
            using (Connection)
            {
                using (DbDataReader dr = comm.ExecuteReader())
                {
                    List<HanhKhach> ret = new List<HanhKhach>();
					while(dr.Read())
					{
						ret.Add(GetCurrent(dr, isDeep));
					}
					return ret;
                }
            }
		}
        public bool ThemHanhKhach(HanhKhach objHK, NguoiDung objND)
        {
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ConnectionString;
                SqlConnection db = new SqlConnection(connStr);
                SqlTransaction transaction;

                db.Open();
                transaction = db.BeginTransaction();
                try
                {

                    var cmd1 = new SqlCommand(_insertSql, db, transaction);
                    SetParam(cmd1, "@MAHK", objHK.MaHanhKhach);
                    SetParam(cmd1, "@TENHK", objHK.TenHanhKhach);
                    SetParam(cmd1, "@NGAYSINH", objHK.NgaySinh);
                    SetParam(cmd1, "@GIOITINH", objHK.GioiTinh);
                    SetParam(cmd1, "@HOCHIEU", objHK.HoChieu);
                    SetParam(cmd1, "@CMND", objHK.CMND);
                    SetParam(cmd1, "@DIACHI", objHK.DiaChi);
                    SetParam(cmd1, "@DIENTHOAI", objHK.DienThoai);
                    SetParam(cmd1, "@EMAIL", objHK.Email);
                    SetParam(cmd1, "@NGAYDANGKY", objHK.NgayDangKy);

                    var cmd2 = new SqlCommand(new NguoiDungDA().GetInsertText, db, transaction);
                    SetParam(cmd2, "@MAND", objND.MaNguoiDung);
                    SetParam(cmd2, "@MATKHAU", objND.MatKhau);
                    SetParam(cmd2, "@HOTENND", objND.HoTenND);
                    SetParam(cmd2, "@LOAIND", (int)objND.LoaiND);
                    SetParam(cmd2, "@MAHK", objND.MaHanhKhach);

                    cmd1.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();

                    transaction.Commit();
                    db.Close();
                    return true;
                }
                catch (SqlException sqlError)
                {
                    transaction.Rollback();
                    db.Close();
                    return false;
                }
                


                
            }
            catch (SqlException ex)
            {
                RollbackTransaction();
                return false;
            }

        }
		public HanhKhach GetByPrimaryKey(bool isDeep, params object[] args)
		{
			DbCommand cmd = GetCommand(_primarSql);
			if(args.Length < 1)
			{
				throw new Exception();
			}
            SetParam(cmd, "@MAHK", args[0]);

            using (Connection)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
					if(dr.Read())
					{
						return GetCurrent(dr, isDeep);
					}
					return null;
                }
            }
		}
        public DbCommand GetCommInsert(HanhKhach entity)
        {
            DbCommand cmd = GetCommand(_insertSql);
            SetParam(cmd, "@MAHK", entity.MaHanhKhach);
            SetParam(cmd, "@TENHK", entity.TenHanhKhach);
            SetParam(cmd, "@NGAYSINH", entity.NgaySinh);
            SetParam(cmd, "@GIOITINH", entity.GioiTinh);
            SetParam(cmd, "@HOCHIEU", entity.HoChieu);
            SetParam(cmd, "@CMND", entity.CMND);
            SetParam(cmd, "@DIACHI", entity.DiaChi);
            SetParam(cmd, "@DIENTHOAI", entity.DienThoai);
            SetParam(cmd, "@EMAIL", entity.Email);
            SetParam(cmd, "@NGAYDANGKY", entity.NgayDangKy);
            return cmd;
        }
		public bool Insert(HanhKhach entity)
		{
            DbCommand cmd = GetCommInsert(entity);		
            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Update(HanhKhach entity)
		{
			DbCommand cmd = GetCommand(_updateSql);
            SetParam(cmd, "@MAHK", entity.MaHanhKhach);
            SetParam(cmd, "@TENHK", entity.TenHanhKhach);
            SetParam(cmd, "@NGAYSINH", entity.NgaySinh);
            SetParam(cmd, "@GIOITINH", entity.GioiTinh);
            SetParam(cmd, "@HOCHIEU", entity.HoChieu);
            SetParam(cmd, "@CMND", entity.CMND);
            SetParam(cmd, "@DIACHI", entity.DiaChi);
            SetParam(cmd, "@DIENTHOAI", entity.DienThoai);
            SetParam(cmd, "@EMAIL", entity.Email);
            SetParam(cmd, "@NGAYDANGKY", entity.NgayDangKy);

			
            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Delete(HanhKhach entity)
		{
			DbCommand cmd = GetCommand(_deleteSql);
            SetParam(cmd, "@MAHK", entity.MaHanhKhach);

            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		#endregion
    }
}


